查询

2023-11-03 09:23| 来源: 网络整理| 查看: 265

检索单个对象

GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误

// 获取第一条记录（主键升序）db.First(&user)// SELECT * FROM users ORDER BY id LIMIT 1;// 获取一条记录，没有指定排序字段db.Take(&user)// SELECT * FROM users LIMIT 1;// 获取最后一条记录（主键降序）db.Last(&user)// SELECT * FROM users ORDER BY id DESC LIMIT 1;result := db.First(&user)result.RowsAffected // 返回找到的记录数result.Error // returns error or nil// 检查 ErrRecordNotFound 错误errors.Is(result.Error, gorm.ErrRecordNotFound)

如果你想避免ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。

对单个对象使用Find而不带limit，db.Find(&user)将会查询整个表并且只返回第一个对象，这是性能不高并且不确定的。

First and Last 方法会按主键排序找到第一条记录和最后一条记录 (分别)。只有在目标 struct 是指针或者通过 db.Model() 指定 model 时，该方法才有效。此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序。例如：

var user Uservar users []User// works because destination struct is passed indb.First(&user)// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1// works because model is specified using `db.Model()`result := map[string]interface{}{}db.Model(&User{}).First(&result)// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1// doesn't workresult := map[string]interface{}{}db.Table("users").First(&result)// works with Takeresult := map[string]interface{}{}db.Table("users").Take(&result)// no primary key defined, results will be ordered by first field (i.e., `Code`)type Language struct { Code string Name string}db.First(&Language{})// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1 根据主键检索

如果主键是数字类型，您可以使用内联条件来检索对象。当使用字符串时，需要额外的注意来避免SQL注入；查看 Security 部分来了解详情。

db.First(&user, 10)// SELECT * FROM users WHERE id = 10;db.First(&user, "10")// SELECT * FROM users WHERE id = 10;db.Find(&users, []int{1,2,3})// SELECT * FROM users WHERE id IN (1,2,3);

如果主键是字符串(例如像uuid)，查询将被写成如下：

db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";

当目标对象有一个主键值时，将使用主键构建查询条件，例如：

var user = User{ID: 10}db.First(&user)// SELECT * FROM users WHERE id = 10;var result Userdb.Model(User{ID: 10}).First(&result)// SELECT * FROM users WHERE id = 10;

NOTE: 如果您使用 gorm 的特定字段类型（例如 gorm.DeletedAt），它将运行不同的查询来检索对象。

type User struct { ID string `gorm:"primarykey;size:16"` Name string `gorm:"size:24"` DeletedAt gorm.DeletedAt `gorm:"index"`}var user = User{ID: 15}db.First(&user)// SELECT * FROM `users` WHERE `users`.`id` = '15' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1 检索全部对象// Get all recordsresult := db.Find(&users)// SELECT * FROM users;result.RowsAffected // returns found records count, equals `len(users)`result.Error // returns error 条件String 条件// Get first matched recorddb.Where("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;// Get all matched recordsdb.Where("name ?", "jinzhu").Find(&users)// SELECT * FROM users WHERE name 'jinzhu';// INdb.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');// LIKEdb.Where("name LIKE ?", "%jin%").Find(&users)// SELECT * FROM users WHERE name LIKE '%jin%';// ANDdb.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;// Timedb.Where("updated_at > ?", lastWeek).Find(&users)// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';// BETWEENdb.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

如果对象设置了主键，条件查询将不会覆盖主键的值，而是用 And 连接条件。例如：

var user = User{ID: 10}db.Where("id = ?", 20).First(&user)// SELECT * FROM users WHERE id = 10 and id = 20 ORDER BY id ASC LIMIT 1

这个查询将会给出record not found错误所以，在你想要使用例如 user 这样的变量从数据库中获取新值前，需要将例如 id 这样的主键设置为nil。

Struct & Map 条件// Structdb.Where(&User{Name: "jinzhu", Age: 20}).First(&user)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;// Mapdb.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;// Slice of primary keysdb.Where([]int64{20, 21, 22}).Find(&users)// SELECT * FROM users WHERE id IN (20, 21, 22);

NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu";

To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:

db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;

For more details, see Specify Struct search fields.

指定结构体查询字段

When searching with struct, you can specify which particular values from the struct to use in the query conditions by passing in the relevant field name or the dbname to Where(), for example:

db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)// SELECT * FROM users WHERE age = 0; 内联条件

Query conditions can be inlined into methods like First and Find in a similar way to Where.

// Get by primary key if it were a non-integer typedb.First(&user, "id = ?", "string_primary_key")// SELECT * FROM users WHERE id = 'string_primary_key';// Plain SQLdb.Find(&user, "name = ?", "jinzhu")// SELECT * FROM users WHERE name = "jinzhu";db.Find(&users, "name ? AND age > ?", "jinzhu", 20)// SELECT * FROM users WHERE name "jinzhu" AND age > 20;// Structdb.Find(&users, User{Age: 20})// SELECT * FROM users WHERE age = 20;// Mapdb.Find(&users, map[string]interface{}{"age": 20})// SELECT * FROM users WHERE age = 20; Not 条件

Build NOT conditions, works similar to Where

db.Not("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;// Not Indb.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");// Structdb.Not(User{Name: "jinzhu", Age: 18}).First(&user)// SELECT * FROM users WHERE name "jinzhu" AND age 18 ORDER BY id LIMIT 1;// Not In slice of primary keysdb.Not([]int64{1,2,3}).First(&user)// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1; Or 条件db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';// Structdb.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);// Mapdb.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.

选择特定字段

Select allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.

db.Select("name", "age").Find(&users)// SELECT name, age FROM users;db.Select([]string{"name", "age"}).Find(&users)// SELECT name, age FROM users;db.Table("users").Select("COALESCE(age,?)", 42).Rows()// SELECT COALESCE(age,'42') FROM users;

Also check out Smart Select Fields

排序

Specify order when retrieving records from the database

db.Order("age desc, name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;// Multiple ordersdb.Order("age desc").Order("name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;db.Clauses(clause.OrderBy{ Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},}).Find(&User{})// SELECT * FROM users ORDER BY FIELD(id,1,2,3) Limit & Offset

Limit specify the max number of records to retrieve Offset specify the number of records to skip before starting to return the records

db.Limit(3).Find(&users)// SELECT * FROM users LIMIT 3;// Cancel limit condition with -1db.Limit(10).Find(&users1).Limit(-1).Find(&users2)// SELECT * FROM users LIMIT 10; (users1)// SELECT * FROM users; (users2)db.Offset(3).Find(&users)// SELECT * FROM users OFFSET 3;db.Limit(10).Offset(5).Find(&users)// SELECT * FROM users OFFSET 5 LIMIT 10;// Cancel offset condition with -1db.Offset(10).Find(&users1).Offset(-1).Find(&users2)// SELECT * FROM users OFFSET 10; (users1)// SELECT * FROM users; (users2)

Refer to Pagination for details on how to make a paginator

Group By & Havingtype result struct { Date time.Time Total int}db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()defer rows.Close()for rows.Next() { ...}rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()defer rows.Close()for rows.Next() { ...}type Result struct { Date time.Time Total int64}db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results) Distinct

Selecting distinct values from the model

db.Distinct("name", "age").Order("name, age desc").Find(&results)

Distinct works with Pluck and Count too

Joins

Specify Joins conditions

type result struct { Name string Email string}db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.idrows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()for rows.Next() { ...}db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)// multiple joins with parameterdb.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "[email protected]").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user) Joins 预加载

You can use Joins eager loading associations with a single SQL, for example:

db.Joins("Company").Find(&users)// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;// inner joindb.InnerJoins("Company").Find(&users)// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` INNER JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

Join with conditions

db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users)// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;

For more details, please refer to Preloading (Eager Loading).

Joins 一个衍生表

You can also use Joins to join a derived table.

type User struct { Id int Age int}type Order struct { UserId int FinishedAt *time.Time}query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest Scan

Scanning results into a struct works similarly to the way we use Find

type Result struct { Name string Age int}var result Resultdb.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)// Raw SQLdb.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)

【本文地址】

公司简介

联系我们